﻿using System.Data;
using HRMSystemShi.Model;
using Microsoft.Data.SqlClient;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace HRMSystemShi.DAL
{
    public class EmployeeService
    {
        public static DataTable GetEmployeeList()
        {
            string sql = "SELECT Employee.Id as ID,Number as 工号,Employee.Name as 姓名,Department.Name as 部门,InDay as 入职时间,Nation as 国籍,NativePlace as 籍贯  FROM Employee,Department Where Employee.DepartmentId=Department.Id";
            return SqlHelper.GetDataTable(sql);
        }
        public static Employee GetEmployeeById(Guid id)
        {
            string sql = "SELECT * FROM Employee Where Id=@Id";
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id));
            Employee employee = new Employee();
            if (reader.Read())
            {
                employee = new Employee
                {
                    Id = (Guid)reader["Id"],
                    Name = reader["Name"].ToString()!,
                    Number = reader["Number"].ToString()!,
                    DepartmentId = (Guid)reader["DepartmentId"],
                    InDay = (DateTime)reader["InDay"],
                    Birthday = (DateTime)reader["Birthday"],
                    MarriageId = (Guid)reader["MarriageId"],
                    PartyId = (Guid)reader["PartyId"],
                    EducationId = (Guid)reader["EducationId"],
                    GenderId = (Guid)reader["GenderId"],
                    Telephone = reader["Telephone"].ToString()!,
                    Address = reader["Address"].ToString()!,
                    Email = reader["Email"].ToString()!,
                    Remarks = reader["Remarks"].ToString(),
                    Resume = reader["Resume"].ToString(),
                    Nation = reader["Nation"].ToString()!,
                    NativePlace = reader["NativePlace"].ToString()!,
                    photo = reader["Photo"] as byte[]
                };
            }
            return employee;
        }

        public static bool AddEmployee(Employee emp)
        {
            string sql = "Insert Into Employee Values(@ID,@Number,@Name,@Birthday,@InDay,@MarriageID,@PartyId,@EducationId,@GenderId,@DepartmentId,@Telephone,@Address,"+
                "@Email,@Remarks,@Resume,@Photo,@Nation,@NativePlace)";
            return SqlHelper.ExecuteNonQuery(sql, GetSqlParameters(emp))>0;
        }

        public static bool UpdateEmployee(Employee emp)
        {
            string sql = "Update Employee Set Number=@Number,Name=@Name,Birthday=@Birthday,InDay=@InDay,MarriageId=@MarriageId,PartyId=@PartyId" +
            ",EducationId=@EducationId,GenderId=@GenderId,DepartmentId=@DepartmentId,Telephone=@Telephone,Address=@Address,Email=@Email,Remarks=@Remarks,Resume=@Resume,Photo=@Photo,Nation=@Nation,NativePlace=@NativePlace Where Id=@ID";
            return SqlHelper.ExecuteNonQuery(sql, GetSqlParameters(emp)) > 0;
        }
        public static SqlParameter[] GetSqlParameters(Employee emp)
        {
            SqlParameter photoParameter = new SqlParameter("@Photo",SqlDbType.Image);
            if(emp.photo != null)
            {
                photoParameter.Value = emp.photo;
            }
            else
            {
                photoParameter.Value = DBNull.Value;
            }

            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@Name",emp.Name),
                new SqlParameter("@ID",emp.Id),
                new SqlParameter("@Number",emp.Number),
                new SqlParameter("@BirthDay",emp.Birthday),
                new SqlParameter("@InDay",emp.InDay),
                new SqlParameter("@MarriageId",emp.MarriageId),
                new SqlParameter("@PartyId",emp.PartyId),
                new SqlParameter("@EducationId",emp.EducationId),
                new SqlParameter("@GenderId",emp.GenderId),
                new SqlParameter("@DepartmentId",emp.DepartmentId),
                new SqlParameter("@Telephone",emp.Telephone),
                new SqlParameter("@Address",emp.Address),
                new SqlParameter("@Email",emp.Email),
                new SqlParameter("@Remarks",emp.Remarks),
                new SqlParameter("@Resume",emp.Resume),
                new SqlParameter("@Nation",emp.Nation),
                new SqlParameter("@NativePlace",emp.NativePlace),
                photoParameter
            };
            return sqlParameters;
        }
        public static DataTable GetEmployeeList(Employee employee)
        {
            string sql = "SELECT Employee.Id as ID,Number as 工号,Employee.Name as 姓名,Department.Name as 部门,InDay as 入职时间,Nation as 国籍,NativePlace as 籍贯  FROM Employee,Department Where Employee.DepartmentId=Department.Id";
            string sqlWhere = "";
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(employee.Name))
            {
                sqlWhere += " And Employee.Name Like @name";
                sqlParameters.Add(new SqlParameter("@name", $"%{employee.Name}%"));
            }
            if(employee.DepartmentId!= Guid.Empty)
            {
                sqlWhere += " And DepartmentId=@departmentId";
                sqlParameters.Add(new SqlParameter("@departmentId", employee.DepartmentId));
            }
            if (employee.InDayFrom != null || employee.InDayTo != null)
            {
                sqlWhere += " And InDay>=@InDayFrom AND InDay<=@InDayTo";
                sqlParameters.Add(new SqlParameter("@InDayFrom", employee.InDayFrom));
                sqlParameters.Add(new SqlParameter("@InDayTo", employee.InDayTo));
            }
            sql+= sqlWhere;
            return SqlHelper.GetDataTable(sql, sqlParameters.ToArray());
        }
        public static bool DeleteEmployeeById(Guid id)
        {
            string sql = "Delete From Employee Where Id=@ID";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@ID", id))>0;
        }
        public static bool ExportEmportEXECL(List<Employee> employees,string filePath)
        {
            IWorkbook workbook=new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();

            ICellStyle headerStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = "SimSun";
            font.FontHeightInPoints = 12;
           // font.Boldweight = (short)FontBoldWeight.Bold;
            headerStyle.SetFont(font);
            headerStyle.FillForegroundColor = IndexedColors.LightBlue.Index;
            headerStyle.FillPattern = FillPattern.SolidForeground;
            headerStyle.Alignment = HorizontalAlignment.Center;

            var headerRow = sheet.CreateRow(0);
            string[] headers = { "序号", "工号", "姓名", "部门", "入职日期","民族","籍贯" };
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (int i = 0; i < employees.Count; i++)
            {
                var row=sheet.CreateRow(i+1);
                row.CreateCell(0).SetCellValue(i+1);
                row.CreateCell(1).SetCellValue(employees[i].Number);
                row.CreateCell(2).SetCellValue(employees[i].Name);
                row.CreateCell(3).SetCellValue(employees[i].Department);
                row.CreateCell(4).SetCellValue(employees[i].InDay.ToString("yyyy年MM月dd日"));
                row.CreateCell(5).SetCellValue(employees[i].Nation);
                row.CreateCell(6).SetCellValue(employees[i].NativePlace);
            }
            // 自动调整列宽
            for (int i = 0; i < headers.Length; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            try
            {
                // 保存Excel文件
                if (filePath != string.Empty)
                {
                    using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fileStream);
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
